USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[ClassesTaught]    Script Date: 7/8/2013 7:20:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[ClassesTaught] @InstructorID int, @year int
AS

--DECLARE @instructorID int = 887969243, @year int = 2013;

WITH cteMain AS
(
	SELECT
		bannerCRN,
		Semester,
		Year,
		SubjectCode,
		CourseNumber,
		ST.instructorID,
		R.studentID,
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0 
		END Score,
		T.ID testID,
		T.testName 
		--SELECT * 
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND ST.instructorID = @InstructorID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND CS.year = @year
		INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
		INNER JOIN Questions Q ON Q.ID = QR.questionID
		INNER JOIN Categories C on C.ID = Q.categoryID
					AND LOWER(C.categoryName) <> 'other' 
		INNER JOIN Tests T ON T.ID = Q.testID 
			AND T.typeID = 5 --Course Eval
		INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
		INNER JOIN Distractors D ON D.ID = DR.distractorID
)


SELECT 
	bannerCRN,
	Semester,
	Year,
	SubjectCode,
	CourseNumber,
	AVG(score) score,
	COUNT(*) responses,
	COUNT(DISTINCT studentID) students,
	STDEVP(score) standardDeviation,
	testName
FROM 
	cteMain
GROUP BY
	bannerCRN,
	Semester,
	Year,
	SubjectCode,
	CourseNumber,
	instructorID,
	testName
GO

